package com.xiia.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;

import com.xiia.util.CheckConstant;
import com.xiia.util.DBConnect;
import com.xiia.util.Pager;
import com.xiia.vo.Check;
import com.xiia.vo.News;
import com.xiia.vo.NewsSort;
import com.xiia.vo.NoticeSort;
import com.xiia.vo.User;

/**
 * 2014-11-4 15:15
 * @author 陈强
 *新闻数据层
 */
public class NewsDao extends BaseDao {
	private static UserDao userDao;
	private static NewsSortDao newsSortDao;
	public NewsDao(){
		userDao = new UserDao();
		newsSortDao = new NewsSortDao();
	}
	/**
	 * 撰写新闻
	 * @param news
	 * @return boolean
	 */
	public boolean addNews(News news ){
		String sql = "insert into t_news(title,content,addTime,lastUpdateTime,status,topFlag,headlineFlag,sortId,userId)"+
				"values(?,?,?,?,?,?,?,?,?)";
		Object[] params = {news.getTitle(),news.getContent(),new Date(),new Date(),1,0,0,news.getNewsSort().getSortId(),news.getUser().getUserId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "select * from t_news_sort where sortId = ?";
			Object[] params2 = {news.getNewsSort().getSortId()};
			//取出所属的新闻分类的noticeNum字段
			NewsSort newsSort = (NewsSort)
					query.query(conn, sql, params2, new BeanHandler(NewsSort.class));
			sql = "update t_news_sort set newsNum = ? where sortId = ?";
			Object[] params3 = {newsSort.getNewsNum() + 1, news.getNewsSort().getSortId()};
			//新闻数量加1
			query.update(conn, sql, params3);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 修改新闻
	 * @param news
	 * @return boolean
	 */
	public boolean updateNews(News news ){
		String sql = "update t_news set title=?,content=?,lastUpdateTime=?,status=?,sortId=? where newsId=?";
		Object[] params = {news.getTitle(),news.getContent(),new Date(),1,news.getNewsSort().getSortId(),news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	public boolean updateNewsS(News news ,NewsSort oldSort){
		String sql = "update t_news set title=?,content=?,lastUpdateTime=?,status=?,sortId=? where newsId=?";
		Object[] params = {news.getTitle(),news.getContent(),new Date(),1,news.getNewsSort().getSortId(),news.getNewsId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "select * from t_news_sort where sortId = ?";
			Object[] params2 = {news.getNewsSort().getSortId()};
			//取出所属的新闻分类的noticeNum字段
			NewsSort newsSort = (NewsSort)
					query.query(conn, sql, params2, new BeanHandler(NewsSort.class));
			sql = "update t_news_sort set newsNum = ? where sortId = ?";
			Object[] params3 = {newsSort.getNewsNum() + 1, news.getNewsSort().getSortId()};
			//新闻数量加1
			query.update(conn, sql, params3);
			sql = "update t_news_sort set newsNum = ? where sortId = ?";
			Object[] params4={oldSort.getNewsNum()-1,oldSort.getSortId()};
			//旧的新闻分类数量减1
			query.update(conn, sql, params4);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		/*int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;*/
	}
	/**
	 * 通过id来删除新闻
	 * @param news
	 * @return boolean
	 */
	public boolean deleteNews(News news){
		String sql = "delete from t_news where newsId=?";
		Object[] params = {news.getNewsId()};
		try {
			conn = DBConnect.getConnect();
			conn.setAutoCommit(false);
			QueryRunner query = new QueryRunner();
			query.update(conn, sql, params);
			sql = "select * from t_news_sort where sortId = ?";
			Object[] params2 = {news.getSortId()};
			//取出所属的新闻分类的newsNum字段
			NewsSort newsSort = (NewsSort)
					query.query(conn, sql, params2, new BeanHandler(NewsSort.class));
			sql = "update t_news_sort set newsNum = ? where sortId = ?";
			Object[] params3 = {newsSort.getNewsNum() - 1, newsSort.getSortId()};
			//新闻数量加1
			query.update(conn, sql, params3);
			conn.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		} finally{
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 通过新闻分类来查找该分类下的所有新闻
	 * @param newsSort
	 * @return News
	 */
	public List<News> findNewsBySort(NewsSort newsSort){
		String sql = "select * from t_news where sortId=? order by topFlag and addTime desc ";
		Object[] params={newsSort.getSortId()};
		List<News> news = new ArrayList<News>();
		Class<News> newsClass = News.class;
		news = find(sql,params,newsClass);
		//封装用户
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
			}
		}
		return news;
	}
	/**
	 * 得到t_news的总行数
	 * @return
	 */
	public int getCount(NewsSort newsSort) {
		String sql = "select count(*) count from t_news where sortId = ?";
		Object[] params = {newsSort.getSortId()};
		return getCountFromTable(sql, params);
	}
	/**
	 * 通过某一个新闻id来查找新闻
	 * @param news
	 * @return Object
	 */
	public News findNews(int newsId){
		String sql = "select * from t_news where newsId=?";
		Object[] params = {newsId};
		Class<News> newsClass = News.class;
		News oneNews = new News();
		oneNews=(News)findObject(sql,params,newsClass);
		oneNews.setUser(userDao.getUserById(oneNews.getUserId()));
		return oneNews;
	}
	/**
	 * 发布新闻
	 * @param news
	 * @return boolean
	 */
	public boolean releaseNews(News news){
		String sql = "update t_news set status=? where newsId=?";
		Object[] params = {news.getStatus(),news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 通过头条表来查找所有新闻
	 * @return List<News>
	 */
	public List<News> getAllNewsByHeadline(){
		String sql = "select * from t_news where newsId in(select newsId from t_news_headline where headFlag=?)";
		Object[] params = {1};
		Class<News> newsClass = News.class;
		List<News> news = new ArrayList<News>();
		news = find(sql,params,newsClass);
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
			}
		}
		return news;
	}
	/**
	 * 得到带有数据的pege对象
	 * @param page
	 * @param orderBy 排序字段
	 * @return 返回带有News集合的page
	 */
	public Pager findNewsPage(NewsSort newsSort,Pager page ) {
		// TODO Auto-generated method stub
		
		String sql = "select * from t_news where sortId=? order by topFlag and addTime desc  limit ?,?";
		Object[] params = {newsSort.getSortId(),(page.getCurrentPage()-1)*page.getPageSize(),page.getPageSize()
				};
		List<News> news = find(sql, params, News.class);
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
			}
		}
		page.setList(news);
		
		return page;
	}
	/**
	 * 查询所有新闻
	 * @param page
	 * @return page
	 */
	public Pager findAllNewsPage(Pager page){
		String sql="select * from t_news order by topFlag and addTime desc limit ?,?";
		Object [] params = {(page.getCurrentPage()-1)*page.getPageSize(),page.getPageSize()};
		List<News> news = find(sql, params, News.class);
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
				int sortId = n.getSortId();
				NewsSort sort = newsSortDao.findNewsSortById(sortId);
				n.setNewsSort(sort);
			}
		}
		page.setList(news);
		
		return page;
	}
	public int getAllNewsCount(){
		String sql ="select count(*) count from t_news";
		return getCountFromTable(sql, null);
	}
	/**
	 * 新闻置顶后修改置顶标记
	 * @param news
	 * @return boolean
	 */
	public boolean updateNewsTopFlag(News news){
		String sql = "update t_news set topFlag=1 where newsId=?";
		Object[] params = {news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 新闻取消置顶后修改置顶标记
	 * @param news
	 * @return boolean
	 */
	public boolean deleteNewsTopFlag(News news){
		String sql = "update t_news set topFlag=0 where newsId=?";
		Object[] params = {news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/** 
	 * 新闻置顶
	 * @param news
	 * @return boolean
	 */
	public boolean updateNewsHeadlineFlag(News news){
		String sql = "update t_news set headlineFlag=1 where newsId=?";
		Object[] params = {news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 新闻取消头条
	 * @param news
	 * @return boolean
	 */
	public boolean deleteNewsHeadlineFlag(News news){
		String sql = "update t_news set headlineFlag=0 where newsId=?";
		Object[] params = {news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 添加发送审核
	 * @param news
	 * @return boolean
	 */
	public boolean addCheck(News news){
		String sql = "insert into t_check(checkedId,flag,type,lastCheckTime,userId)values(?,?,?,?,?)";
		Object[] params={news.getNewsId(),0,CheckConstant.TYPE_NEWS,new Date(),news.getUser().getUserId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 修改审核字段
	 * @param news
	 * @return boolean
	 */
	public boolean updateCheck(News news){
		String sql="update set t_check flag=0 where checkedId=?";
		Object[] params  ={news.getNewsId()};
		int c = update(sql,params);
		if(c>0)
			return true;
		else
			return false;
	}
	/**
	 * 通过ID来查找审核表中的新闻
	 * @param news
	 * @return boolean
	 */
	public boolean findCheckByNewsId(News news){
		String sql="select * from t_check where checkedId=?";
		Object[] params={news.getNewsId()};
		Class<Check> newsCheckClass = Check.class;
		Check checkNews = new Check();
		checkNews=(Check)findObject(sql,params,newsCheckClass);
		if(checkNews==null)
			return true;
		else{
			checkNews.setUser(userDao.getUserById(checkNews.getUserId()));
			return false;
		}
	}
	/**
	 * 通过ID来查找审核表中的新闻
	 * @param news
	 * @return Check
	 */
	public Check findCheckByNewsIdCheck(News news){
		String sql="select * from t_check where checkedId=?";
		Object[] params={news.getNewsId()};
		Class<Check> newsCheckClass = Check.class;
		Check checkNews = new Check();
		checkNews=(Check)findObject(sql,params,newsCheckClass);
		checkNews.setUser(userDao.getUserById(checkNews.getUserId()));
		return checkNews;
	}
	/**
	 * 得到未通过的新闻的数量
	 * @return int
	 */
	public int getCountNoPass() {
		String sql = "select count(*) count from t_news where status=4";
		return getCountFromTable(sql, null);
	}
	/**
	 * 得到未通过新闻的集合添加到page中
	 * @param page
	 * @return Pager
	 */
	public Pager findNewsNoPassPage(Pager page ) {
		// TODO Auto-generated method stub
		
		String sql = "select * from t_news where status=4 order by topFlag and addTime desc  limit ?,?";
		Object[] params = {(page.getCurrentPage()-1)*page.getPageSize(),page.getPageSize()
				};
		List<News> news = find(sql, params, News.class);
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
			}
		}
		page.setList(news);
		
		return page;
	}
	/**
	 * 通过新闻分类来查找已发布的新闻封装到Pager中
	 * @param newsSort
	 * @param page
	 * @return Pager
	 */
	public Pager findReleaseNewsBySort(NewsSort newsSort,Pager page){
		String sql = "select * from t_news where sortId=? and status=5 order by topFlag and addTime desc limit ?,? ";
		Object[] params={newsSort.getSortId(),(page.getCurrentPage()-1)*page.getPageSize(),page.getPageSize()};
		List<News> news = new ArrayList<News>();
		Class<News> newsClass = News.class;
		news = find(sql,params,newsClass);
		//封装用户
		if(null != news){
			for(News n : news){
				n.setUser(userDao.getUserById(n.getUserId()));
			}
		}
		page.setList(news);
		return page;
	}
	/**
	 * 通过分类得到已发布的数量
	 * @param newsSort
	 * @return int
	 */
	public int getConuntRelease(NewsSort newsSort){
		String sql = "select count(*) count from t_news where status=5 and sortId=? ";
		Object[] Params = {newsSort.getSortId()};
		return getCountFromTable(sql, Params);
	}
	
}
